# -*- coding: utf-8 -*-

import sqlite3
import time
import urllib2
import csv
from BeautifulSoup import BeautifulSoup

def makeHTMLPage(name, title, bodyHTML):
    ficheiro = file( name, "w" )

    beginHTML = "<html><head><title>{0}</title></head><body>".format( title )
    endHTML = "</body></html>"
    
    pageHTML = beginHTML + bodyHTML + endHTML
    pageHTML = BeautifulSoup( ''.join( pageHTML ) )
    pageHTML = pageHTML.prettify()
    
    ficheiro.write( pageHTML )
    ficheiro.close()
    pass

#http://www.w3schools.com/html/html_tables.asp
def tableHTML(title, data):
    title = '''<font size="3" color="red" face="verdana"> {0} </font>'''.format( title )
    table = "<table border = 2>"
    
    for i in range( 0, len ( data ) ):
        table +="<tr>"
        for j in range( 0, len( data[ i ] ) ):
            table += "<td> {0} </td>".format( data[ i ][ j ] )
            pass
        table += "</tr>"
        pass
    table += "</table><br><br>"
    return title + table
    pass


t0 = time.time()
##############################################################################################################################
conn = sqlite3.connect( "rebides.db" )
c = conn.cursor()

data = c.execute( '''select * from teachers''').fetchall()

names = []
c.execute( '''select distinct "Nome do Estabelecimento" from teachers''' )

for row in c:
    names.append( row[ 0 ] )
    pass


acad_years = []
c.execute( '''select distinct "Ano Lectivo" from teachers''' )

for row in c:
    acad_years.append( row[ 0 ] )
    pass


categorys = []
c.execute( '''select distinct "Categoria" from teachers''' )

for row in c:
    categorys.append( row[ 0 ] )
    pass


categorys = []
c.execute( '''select distinct "Categoria" from teachers''' )

for row in c:
    categorys.append( row[ 0 ] )
    pass


grades = []
c.execute( '''select distinct "Ultimo Grau" from teachers''' )

for row in c:
    grades.append( row[ 0 ] )
    pass

conn.close()
print time.time() - t0
##############################################################################################################################
t0 = time.time()
##########################################################################################################################
##title = "Number of teachers in the higher education system per establishment per year"
stats = []
count = 0
for i in range( 0, len( names ) ):
    for j in range(0, len( acad_years ) ):
        stats.append( [ names[ i ] ])
        stats[ count ].append( acad_years[ j ] )
        stats[ count ].append( len( filter( lambda x: ( x[ 6 ] == acad_years[ j ] ) & ( x[ 8 ] == names[ i ] ) , data ) ) )
        count += 1
        pass
    pass
##########################################################################################################################
#http://pt.kioskea.net/faq/10376-python-ler-e-gravar-arquivos-csv
writer = csv.writer( open( 'Stats1.csv', 'a' ) )
writer.writerows( stats )
print time.time() - t0
##print
######################################################################################################################################
####title = "Number of teachers divided per categories per establisment and per year"
##stats = []
##count = 0
##for i in range( 0, 3):#len( names ) ):
##    for j in range( 0, len( acad_years ) ):
##        for k in range( 0, len( categorys ) ):
##            stats.append( [ names [ i ] ] )
##            stats[ count ].append( acad_years[ j ] )
##            stats[ count ].append( categorys[ k ] )
##            stats[ count ].append( len( filter( lambda x: ( x[ 4 ] == categorys[ k ] ) & ( x[ 6 ] == acad_years[ j ] ) & ( x[ 8 ] == names[ i ] ) , data ) ) )
##            print stats[ count ]
##            count += 1
##        pass
##    pass
##########################################################################################################################################
##print
####################################################################################################################################
####title = "Number of teachers divided per categories per establisment and per year"
##stats = []
##count = 0
##for i in range( 0, 3):#len( names ) ):
##    for j in range( 0, len( acad_years ) ):
##        for k in range( 0, len( grades ) ):
##            stats.append( [ names [ i ] ] )
##            stats[ count ].append( grades[ k ] )
##            stats[ count ].append( len( filter( lambda x: ( x[ 2 ] == grades[ k ] ) & ( x[ 6 ] == acad_years[ j ] ) & ( x[ 8 ] == names[ i ] ) , data ) ) )
##            stats[ count ].append( acad_years[ j ] )
##            print stats[ count ]
##            count += 1
##        pass
##    pass
######################################################################################################################################
print time.time() - t0




################################################################################################################################
##t0 = time.time()
##title = "Teachers in the higher education system per establishment per year"
##stats = []
##count = 0
##for i in range( 0, 1):#len( names ) ):
##    for j in range(0, len( acad_years ) ):
##        aux = ( filter( lambda x: ( x[ 6 ] == acad_years[ j ] ) & ( x[ 8 ] == names[ i ] ) , data ) )
##        for k in range( 0, len( aux ) ):
##            stats.append( [ names[ i ] ] )
##            stats[ count ].append( acad_years[ j ] )
##            stats[ count ].append( aux[ k ][ 1 ] )
##            count += 1
##            pass
##        pass
##    pass
################################################################################################################################
##pageHTML = tableHTML( title, stats )
##
##################################################################################################################################
##title = "Teachers divided per categories per establisment and per year"
##stats = []
##count = 0
##for i in range( 0, 1):#len( names ) ):
##    for j in range(0, len( acad_years ) ):
##        for k in range(0, len( categorys ) ):
##            aux = filter( lambda x: ( x[ 4 ] == categorys[ k ] ) & ( x[ 6 ] == acad_years[ j ] ) & ( x[ 8 ] == names[ i ] ) , data )
##            for m in range( 0, len( aux ) ):
##                stats.append( [ names[ i ] ] )
##                stats[ count ].append( acad_years[ j ] )
##                stats[ count ].append( categorys[ k ] )
##                stats[ count ].append( aux[ m ][ 1 ] )
##                count += 1
##                pass
##            pass
##        pass
##    pass
################################################################################################################################
##pageHTML += tableHTML( title, stats )
##
####################################################################################################################################
##title = "Teachers divided per type of degree per establishment and per year"
##stats = []
##count = 0
##for i in range( 0, 1):#len( names ) ):
##    for j in range(0, len( acad_years ) ):
##        for k in range(0, len( grades ) ):
####            aux = c.execute( '''select * from teachers where "Nome do Estabelecimento" = "{0}" AND "Ano Lectivo" = "{1}" AND "Ultimo Grau" = "{2}"'''
####                        .format( names[ i ], acad_years[ j ], grades[ k ] ) ).fetchall()
##            aux = filter( lambda x: ( x[ 2 ] == grades[ k ] ) & ( x[ 6 ] == acad_years[ j ] ) & ( x[ 8 ] == names[ i ] ) , data )
##            for m in range( 0, len( aux ) ):
##                stats.append( [ names[ i ] ] )
##                stats[ count ].append( acad_years[ j ] )
##                stats[ count ].append( grades[ k ] )
##                stats[ count ].append( aux[ m ][ 1 ] )
##                count += 1
##                pass
##            pass
##        pass
##    pass
##################################################################################################################################
##pageHTML += tableHTML( title, stats )
##makeHTMLPage( "index.html", "Statistics", pageHTML )
##print time.time() - t0
